package com.example.demo.timu.tianyiyun;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;

@Repository
public interface TikuRepository extends JpaRepository<Tiku, Long> {

    @Query(value = "SELECT * FROM tiku where question like ?1 limit 5", nativeQuery = true)
    List<Tiku> getTiku(String obj);


    /**
     * 查找重复的问题
     *
     * @return
     */
    @Query(value = "select  question  from  tiku  group  by  question  having  count(question) > 1", nativeQuery = true)
    List<String> findSameQuestion();

    /**
     * 删除重复的问题，保留id小的
     */
    @Transactional
    @Modifying
    @Query(value = "delete from tiku \n" +
            "where question  IN \n" +
            "(SELECT question FROM \n" +
            "(select  question  from tiku b group  by  question   having  count(question) > 1) b)\n" +
            "and id not IN \n" +
            "(SELECT id FROM \n" +
            "(select min(id) id from  tiku a group by question  having count(question)>1) b) and answer is null", nativeQuery = true)
    void deletedSame();


}